# 示例字段处理
import pandas as pd
from datetime import datetime
import matplotlib.pyplot as plt
from sklearn.preprocessing import StandardScaler
from sklearn.cluster import KMeans
# 读取数据
df = pd.read_csv('lilylikes-cart-20250403.csv')

# 1. 时间特征提取
df['first_new_date'] = pd.to_datetime(df['first_new_date'],format='%Y-%m-%d')
df['days_since_new'] = (pd.to_datetime(df['static_date_id'],format='%Y%m%d') - df['first_new_date']).dt.days

# 2. 分类特征编码
# df = pd.get_dummies(df, columns=['color', 'size', 'taobao_class_name'])

# 3. 价格特征
# df['price_diff'] = df['new_price'] - df['daily_price']
# df['price_ratio'] = df['new_price'] / (df['daily_price'] + 1e-6)  # 防止除零

# 4. 行为特征（假设数据包含多日记录）
# 计算商品在首次上新后7天的平均行为
filtered_df = df[df['days_since_new'].between(-7,-1)]
agg_features = filtered_df.groupby('sku_id').agg(
    cart_cnt_7d_avg=('cart_cnt', 'mean'),
    pay_itm_cnt_7d_avg=('pay_itm_cnt', 'mean'),
    # price_diff_7d_avg=('price_diff', 'mean')
)
df = df.merge(agg_features, on='sku_id', suffixes=('', '_7d_avg'))


selected_features = [
    'cart_cnt_7d_avg',    # 加购量反映用户兴趣
    'pay_itm_cnt_7d_avg', # 实际转化率
    # 'price_diff',  # 价格变动幅度
    # 'price_ratio',        # 价格变动比例
    'days_since_new',     # 上新后的时间进度
    # 添加编码后的分类特征（如color_red, size_M等）
]



# 数据标准化
scaler = StandardScaler()
X_scaled = scaler.fit_transform(df[selected_features])

# 通过肘部法则选择最佳K值
sse = []
for k in range(2, 15):
    kmeans = KMeans(n_clusters=k, random_state=42)
    kmeans.fit(X_scaled)
    sse.append(kmeans.inertia_)

# 可视化选择（通常选择拐点）
plt.plot(range(2,15), sse, 'bx-')
plt.xlabel('Number of clusters (k)')
plt.ylabel('SSE')
plt.title('Elbow Method')
plt.show()


